import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDAO {
    public boolean addGrade(Grade grade) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO grade(student_id, course_id, score) VALUES(?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, grade.getStudentId());
            stmt.setString(2, grade.getCourseId());
            stmt.setDouble(3, grade.getScore());
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt);
        }
    }

    public boolean updateGrade(Grade grade) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE grade SET score=? WHERE student_id=? AND course_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setDouble(1, grade.getScore());
            stmt.setString(2, grade.getStudentId());
            stmt.setString(3, grade.getCourseId());
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt);
        }
    }

    public boolean deleteGrade(String studentId, String courseId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM grade WHERE student_id=? AND course_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            stmt.setString(2, courseId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt);
        }
    }

    public List<Grade> getAllGrades() {
        List<Grade> grades = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM grade";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setStudentId(rs.getString("student_id"));
                grade.setCourseId(rs.getString("course_id"));
                grade.setScore(rs.getDouble("score"));
                grades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return grades;
    }

    public Grade getGrade(String studentId, String courseId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM grade WHERE student_id=? AND course_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            stmt.setString(2, courseId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                Grade grade = new Grade();
                grade.setStudentId(rs.getString("student_id"));
                grade.setCourseId(rs.getString("course_id"));
                grade.setScore(rs.getDouble("score"));
                return grade;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return null;
    }

    public List<Grade> getGradesByStudent(String studentId) {
        List<Grade> grades = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM grade WHERE student_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setStudentId(rs.getString("student_id"));
                grade.setCourseId(rs.getString("course_id"));
                grade.setScore(rs.getDouble("score"));
                grades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return grades;
    }

    public List<Grade> getGradesByCourse(String courseId) {
        List<Grade> grades = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM grade WHERE course_id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, courseId);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Grade grade = new Grade();
                grade.setStudentId(rs.getString("student_id"));
                grade.setCourseId(rs.getString("course_id"));
                grade.setScore(rs.getDouble("score"));
                grades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return grades;
    }
}
